use [Authentication];

/****** Object:  Table [dbo].[User]    Script Date: 9/21/2015 2:46:01 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

----------------CREATE TABLE APPLICATION-----
CREATE TABLE [dbo].[Application](
	[ApplicationKey] [uniqueidentifier] NOT NULL,
	[Name] [varchar](128) NOT NULL,
	[CreatedUTC] [smalldatetime] NOT NULL,
	[ModifiedUTCDate] [smalldatetime] NOT NULL,
	[Description] [nvarchar](1024) NULL,
 CONSTRAINT [PK_Application_1] PRIMARY KEY CLUSTERED 
(
	[ApplicationKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



----------------CREATE TABLE USER----------
CREATE TABLE [User](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](256) NOT NULL,
	[FirstName] [varchar](128) NULL,
	[LastName] [varchar](128) NULL,
	[MobileAlias] [nvarchar](16) NULL,
	[MobilePIN] [nvarchar](16) NULL,
	[Email] [nvarchar](256) NULL,
	[IsAnonymous] [bit] NOT NULL,
	[Password] [nvarchar](128) NOT NULL,
	[IsApproved] [bit] NOT NULL,
	[IsLockedOut] [bit] NOT NULL,
	[CreateUTCDate] [datetime] NOT NULL,
	[FailedPasswordAttemptCount] [int] NOT NULL,
	[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
	[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
	[FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
	[PasswordExpirationUTCDate] [smalldatetime] NULL,
	[ModuleConfigurationID] [int] NULL,
	[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_User_IsDeleted]  DEFAULT ((0)),
	[UserIdentifier] [varchar](256) NULL,
	[CanContactByEmail] [bit] NULL,
	[IsSuspended] [bit] NULL DEFAULT ((0)),
	[LastPasswordChangedDate] [datetime] NULL,
 CONSTRAINT [PK_aspnet_Membership] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 


----------------CREATE TABLE ACCOUNT----------

CREATE TABLE [Account](
	[AccountID] [bigint] IDENTITY(1,1) NOT NULL,
	[ApplicationKey] [uniqueidentifier] NOT NULL,
	[Description] [nvarchar](2048) NOT NULL,
	[CreatedUTCDate] [smalldatetime] NOT NULL,
	[AccountIdentifier] [varchar](256) NULL,
 CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
(
	[AccountID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


------------------CREATE TABLE ACCOUNT_USER---------------
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [AccountUser](
	[AccountUserID] [bigint] IDENTITY(1,1) NOT NULL,
	[AccountID] [bigint] NOT NULL,
	[UserID] [int] NOT NULL,
 CONSTRAINT [PK_AccountUser] PRIMARY KEY CLUSTERED 
(
	[AccountUserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [AccountUser]  WITH CHECK ADD  CONSTRAINT [FK_AccountUser_Account] FOREIGN KEY([AccountID])
REFERENCES [Account] ([AccountID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [AccountUser] CHECK CONSTRAINT [FK_AccountUser_Account]
GO

ALTER TABLE [AccountUser]  WITH CHECK ADD  CONSTRAINT [FK_AccountUser_User] FOREIGN KEY([UserID])
REFERENCES [User] ([UserID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [AccountUser] CHECK CONSTRAINT [FK_AccountUser_User]
GO


-----------------CREATR TABLE ROLE-------------------------------
CREATE TABLE [Role](
	[RoleID] [int] IDENTITY(1,1) NOT NULL,
	[RoleName] [nvarchar](256) NOT NULL,
	[Description] [nvarchar](256) NULL,
	[ApplicationKey] [uniqueidentifier] NOT NULL,
	[SortOrder] [int] NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
	[RoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

----------------------CREATE TABLE PROFILE---------------------------------SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [Profile](
	[ProfileID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL,
	[PropertyName] [nvarchar](256) NOT NULL,
	[PropertyValuesString] [nvarchar](max) NOT NULL,
	[PropertyValuesBinary] [varbinary](max) NOT NULL,
	[LastUpdatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Profile] PRIMARY KEY CLUSTERED 
(
	[ProfileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [Profile]  WITH CHECK ADD  CONSTRAINT [FK_Profile_User] FOREIGN KEY([UserID])
REFERENCES [User] ([UserID])
ON DELETE CASCADE
GO

ALTER TABLE [Profile] CHECK CONSTRAINT [FK_Profile_User]
GO
-------------------------CREATE TABLE USER_IN_ROLE------------

CREATE TABLE [UserInRole](
	[UserInRoleID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL,
	[RoleID] [int] NOT NULL,
 CONSTRAINT [PK_UserInRole] PRIMARY KEY CLUSTERED 
(
	[UserInRoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [UserInRole]  WITH CHECK ADD  CONSTRAINT [FK_UserInRole_Membership] FOREIGN KEY([UserID])
REFERENCES [User] ([UserID])
ON DELETE CASCADE
GO

ALTER TABLE [UserInRole] CHECK CONSTRAINT [FK_UserInRole_Membership]
GO

ALTER TABLE [UserInRole]  WITH CHECK ADD  CONSTRAINT [FK_UserInRole_Role] FOREIGN KEY([RoleID])
REFERENCES [Role] ([RoleID])
GO

ALTER TABLE [UserInRole] CHECK CONSTRAINT [FK_UserInRole_Role]
GO

---------------------CREATE TABLE USER_ACTIVITY--------------

CREATE TABLE [UserActivity](
	[UserActivityID] [bigint] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL,
	[ActingUserID] [int] NULL,
	[CreatedUTCDate] [datetime] NULL,
	[ActivityType] [smallint] NOT NULL,
	[Note] [nvarchar](max) NULL,
	[IP1] [tinyint] NOT NULL,
	[IP2] [tinyint] NOT NULL,
	[IP3] [tinyint] NOT NULL,
	[IP4] [tinyint] NOT NULL,
	[UserAgent] [nvarchar](2048) NULL,
	[ApplicationKey] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_UserActivity] PRIMARY KEY CLUSTERED 
(
	[UserActivityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [UserActivity]  WITH CHECK ADD  CONSTRAINT [FK_UserActivity_User] FOREIGN KEY([UserID])
REFERENCES [User] ([UserID])
ON DELETE CASCADE
GO

ALTER TABLE [UserActivity] CHECK CONSTRAINT [FK_UserActivity_User]
GO

ALTER TABLE [UserActivity]  WITH CHECK ADD  CONSTRAINT [FK_UserActivity_User1] FOREIGN KEY([ActingUserID])
REFERENCES [User] ([UserID])
GO

ALTER TABLE [UserActivity] CHECK CONSTRAINT [FK_UserActivity_User1]
GO

------------------------------CREATE TABLE AccountUserRole

CREATE TABLE [AccountUserRole](
	[AccountUserRoleID] [int] IDENTITY(1,1) NOT NULL,
	[AccountId] [bigint] NOT NULL,
	[RoleID] [int] NOT NULL,
	[UserID] [int] NOT NULL,
 CONSTRAINT [PK_AccountUserRole] PRIMARY KEY CLUSTERED 
(
	[AccountUserRoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_AccountUserRole] UNIQUE NONCLUSTERED 
(
	[AccountId] ASC,
	[RoleID] ASC,
	[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [AccountUserRole]  WITH CHECK ADD  CONSTRAINT [FK_AccountUserRole_Account] FOREIGN KEY([AccountId])
REFERENCES [Account] ([AccountID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [AccountUserRole] CHECK CONSTRAINT [FK_AccountUserRole_Account]
GO

ALTER TABLE [AccountUserRole]  WITH CHECK ADD  CONSTRAINT [FK_AccountUserRole_Role] FOREIGN KEY([RoleID])
REFERENCES [Role] ([RoleID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [AccountUserRole] CHECK CONSTRAINT [FK_AccountUserRole_Role]
GO

ALTER TABLE [AccountUserRole]  WITH CHECK ADD  CONSTRAINT [FK_AccountUserRole_User] FOREIGN KEY([UserID])
REFERENCES [User] ([UserID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [AccountUserRole] CHECK CONSTRAINT [FK_AccountUserRole_User]
GO

